The password you use to connect to the server is not saved when you import data from a server by using ODBC Connection in Excel

Symptoms

When you try to import data from a network server in Microsoft Excel, the password you use to connect to the server is not saved. This occurs even though you clicked to select the Save password in file check box.

Cause

The Allow saving password check box was not selected on the Connection tab of the Data Link Properties dialog box, when the connection to the data source was made.

Resolution

Click to select the Allow saving password check box when you are making the connection to the data source. To save the password with the file, follow these steps:

1. Start Excel and open a new blank workbook.

2. On the Data menu, point to Import External Data, and then click Import Data.
   
   Note In Microsoft Office Excel 2007, click From Other Source in the Get External Data group on the Data tab, and then click From Data Connection Wizard.

3. In the Select Data Source dialog box, click New Source.
   
   Note In Excel 2007, skip this step.

4. In step 1 of the Data Connection Wizard, under What kind of data source do you want to connect to, click Other/Advanced, and then click Next.

5. In the Data Link Properties dialog box, on the Provider tab, click Microsoft OLE DB Provider for ODBC Drivers, and then click Next.

6. On the Connection tab, under Specify the source of the data, select a System DSN data source, type the user name and password, and then click to select the Allow saving password check box. Under Enter the initial catalog to use, select a database on the server, and then click OK.

7. In the Choose Data step of the Data Connection Wizard, select a table, and then click Next.

8. In the Finish step of the wizard, click to select the Save password in file check box. You receive a warning about how to save the password without encryption. Click Yes, and then click Finish.

9. In the Select Data Source dialog box, take note of the filename that is selected, and then click Open.

10. In the Import Data dialog box, click OK to accept the defaults.
    
    Notice that the data is displayed in the active worksheet.

11. Click Start, point to Search, and then click For Files or Folders.

12. In the Search Results dialog box, search for files named *.odc.

13. Right-click the .odc file that has the same name as the data source that you opened in step 14, and then click Edit in Notepad.

14. In Notepad, click Find on the Edit menu. In the Find what box, type ConnectionString, and then click Find Next.
    
    Notice that this line of html code contains "Password=MyPassword." In this code, MyPassword is the data source password that you saved with the file.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.